﻿
CREATE PROCEDURE [dbo].[fares_GenereazaJurnalVanzari] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@cumpTI bit
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
docd.*
FROM 
Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
JOIN 
(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=11 THEN ValoareTotala ELSE ValoareTotala+ValoareTVA END ) ValoareTotalaSiTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN ValoareTVA ELSE 0 END) TVA24,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza19,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN ValoareTVA ELSE 0 END) TVA19,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) InversBaza,
  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN ValoareTVA ELSE 0 END) InversTVA,
  SUM(CASE WHEN dd.IDRegimFiscal IN (10) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) DeducereNoRO,
  SUM(CASE WHEN dd.IDRegimFiscal IN (13) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) FaraDeducereNoRO,
  SUM(CASE WHEN dd.IDRegimFiscal IN (14) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) IntracomScutite1,
  SUM(CASE WHEN dd.IDRegimFiscal IN (15) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) IntracomScutite2,
  SUM(CASE WHEN dd.IDRegimFiscal IN (1) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) ScutiteCuDed,
  SUM(CASE WHEN dd.IDRegimFiscal IN (2) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) ScutiteFaraDed,
  SUM(CASE WHEN dd.IDRegimFiscal IN (16) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) Neimpozabile,
  CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END TI

   FROM DocumenteDetaliu dd join Documente a on a.IDDocument=dd.IDDocument
   GROUP BY dd.IDDocument,CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END) docd ON doc.IDDocument = docd.IDDocument
   WHERE ((tipd.IDTipDocument in (1,11)) OR (@cumpTI=1 AND tipd.IDTipDocument in (0) AND TI=1))
    AND doc.CodUnitate=@CodUnitate 
    AND doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
   ORDER BY DataDoc
END